import pandas as pd
import numpy as np
from scipy.optimize import minimize_scalar

def fit_attrition_curve(path):
    # 跳过首行分组标题，从第二行读取实际表头
    df = pd.read_excel(path, header=1)
    # 重命名第一列为 loan_rate
    df = df.rename(columns={df.columns[0]: 'loan_rate'})
    # 剩余列为各信誉等级的流失率
    rating_cols = df.columns[1:]
    churn_funcs = {}
    for col in rating_cols:
        rates = df['loan_rate'].astype(float)
        churn = df[col].astype(float)
        coeffs = np.polyfit(rates, churn, deg=2)
        churn_funcs[col] = lambda r, c=coeffs: np.polyval(c, r)
    return churn_funcs


def optimize_rate(churn_func, rate_bounds=(0.02, 0.2)):
    def neg_revenue(r):
        return -(r * (1 - churn_func(r)))
    res = minimize_scalar(neg_revenue, bounds=rate_bounds, method='bounded')
    return res.x, -res.fun

if __name__ == '__main__':
    path = '../data/附件3：银行贷款年利率与客户流失率关系的统计数据.xlsx'
    churn_funcs = fit_attrition_curve(path)
    for rating, func in churn_funcs.items():
        r_opt, rev_opt = optimize_rate(func)
        print(f"评级 {rating} 最优利率: {r_opt:.4f}, 最大预期收益: {rev_opt:.4f}")